USE SAS
GO

IF EXISTS (SELECT 1 from sysobjects where xtype = 'FN' AND NAME = 'GetDivisionsXml')
	DROP FUNCTION dbo.GetDivisionsXml
GO


CREATE FUNCTION [dbo].[GetDivisionsXml] () 
RETURNS VARCHAR(MAX)
AS
BEGIN
	
	declare 
		@result			varchar(max)

	SET @result = '<DIVISIONS><entry id="0"></entry>'

	select @result = @result + '<entry id="' + CAST([PartnerDivision] AS VARCHAR) + '"><![CDATA[' +  DivisionDesc + ']]></entry>'
	FROM TR_PARTNERDIVISION Order by DivisionDesc
	
	SET @result = @result + '</DIVISIONS>'

	return @result

END


-- SELECT dbo.GetDivisionsXml()